﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.OleDb;

namespace Zuordnung {
    public class ExcelReader001 {
        public List<String> workSheetNames = new List<String>();   //List of Worksheet names 
        private string fileName;  //Path and file name of the Excel Workbook
        private string connectionString;

        public ExcelReader001(string FileName) {
            this.fileName = FileName;
            OpenExcelFile();
        }



        /// <summary>
        /// Open the Excel Workbook
        /// </summary>
        /// <param name="isOpenXMLFormat">Is the file Open XML Format (Excel 2007)</param>
        public void OpenExcelFile() {
            //open the excel file using OLEDB
            OleDbConnection _connection;

            bool isOpenXMLFormat = fileName.EndsWith(".xlsx");
            if (isOpenXMLFormat)
                //read a 2007 file
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
            else
                //read a 97-2003 file
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";

            _connection = new OleDbConnection(connectionString);
            _connection.Open();

            //get all the available sheets
            System.Data.DataTable dataSet = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            //get the number of sheets in the file
            workSheetNames = new List<String>();
            foreach (DataRow row in dataSet.Rows) {
                String workSheetName = row["TABLE_NAME"].ToString();
                if (workSheetName.EndsWith("$")) {
                    // remove the $ sign at the end
                    workSheetNames.Add(workSheetName.Trim(new[] { '$' }));
                }
            }

            if (_connection != null) {
                _connection.Close();
                _connection.Dispose();
            }

            if (dataSet != null)
                dataSet.Dispose();
        }

        /// <summary>
        /// Returns the contents of the sheet
        /// </summary>
        /// <param name="worksheetName">The sheet's name in a string</param>
        /// <returns>A DataTable containing the data</returns>
        public System.Data.DataTable GetWorksheet(string worksheetName) {
            OleDbConnection con = new System.Data.OleDb.OleDbConnection(connectionString);
            OleDbDataAdapter cmd = new System.Data.OleDb.OleDbDataAdapter("select * from [" + worksheetName + "$]", con);

            con.Open();
            System.Data.DataSet excelDataSet = new DataSet();
            bool excelDataSetIsFilled = false;
            try {
                // Fill is nicht das schlaueste.
                // Es gibt aber noch viel mehr.
                cmd.Fill(excelDataSet);
                excelDataSetIsFilled = true;
                //System.Windows.Forms.MessageBox.Show("filled");
            } catch (Exception e) {
                System.Windows.Forms.MessageBox.Show(e.Message);
            }
            con.Close();

            if (excelDataSetIsFilled)
                return excelDataSet.Tables[0];
            else
                return null;
        }



    }
}
